﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;

namespace SixthClassAssignment.DAL{
    public class SqlDao{
        private static SQLiteConnection _con;

        static SqlDao(){
            var path = "Data Source=D:/CSharpProject/Classwork/SixthClassAssignment/resources/student_management.sqlite";
            var strCon = path + ";Pooling=true;FailIfMissing=false";
            _con = new SQLiteConnection(strCon);
        }
        public static int InsertStudent(Student stu){
            _con.Open();
            int count = 0;
            try {
                SQLiteCommand cmd = new SQLiteCommand(_con);
                //cmd.CommandText = "insert into student(stu_no,stu_name,cno,stu_sex) values('01','李华','03','男')";
                cmd.CommandText = "insert into student(stu_no,stu_name,cno,stu_sex) values(@no,@name,@clsNo,@sex)";
                cmd.Prepare();
                cmd.Parameters.AddWithValue("@no", stu.No);
                cmd.Parameters.AddWithValue("@name", stu.Name);
                cmd.Parameters.AddWithValue("@clsNo", stu.ClassNo);
                cmd.Parameters.AddWithValue("@sex", stu.Sex);
                count = cmd.ExecuteNonQuery();
            }
            catch (Exception e) {
            }
            finally {
                _con.Close();
            }
            
            return count;
        }
        public static int InsertSchool(School sch){
            int count = 0;
            _con.Open();
            try {
                SQLiteCommand cmd = new SQLiteCommand(_con);
                cmd.CommandText = "insert into school(sno,sname,slocation) values(@no,@name,@location)";
                cmd.Prepare();
                cmd.Parameters.AddWithValue("@no", sch.No);
                cmd.Parameters.AddWithValue("@name", sch.Name);
                cmd.Parameters.AddWithValue("@location", sch.Location);
                count = cmd.ExecuteNonQuery();
            }
            catch (Exception e) {
            }
            finally {
                _con.Close();
            }
            return count;
        }
        public static int InsertClass(Class cla){
            int count = 0;
            _con.Open();
            try {
                SQLiteCommand cmd = new SQLiteCommand(_con);
                cmd.CommandText = "insert into class(cno,cname,sno) values(@no,@name,@sno)";
                cmd.Prepare();
                cmd.Parameters.AddWithValue("@no", cla.No);
                cmd.Parameters.AddWithValue("@name", cla.Name);
                cmd.Parameters.AddWithValue("@sno", cla.SchNo);
                count = cmd.ExecuteNonQuery();
            }
            catch (Exception) {
            }
            finally {
                _con.Close();
            }

            return count;
        }
        public static int DeleteStudentByNo(string id){
            _con.Open();
            int count = 0;
            try {
                SQLiteCommand cmd = new SQLiteCommand(_con);
                cmd.CommandText = "delete from student where stu_no = @no";
                cmd.Prepare();
                cmd.Parameters.AddWithValue("@no", id);
                count = cmd.ExecuteNonQuery();
            }
            catch (Exception) {
            }
            finally {
                _con.Close();
            }
            return count;
        }
        public static int DeleteClassByNo(string id){
            _con.Open();
            int count = 0;
            try {
                SQLiteCommand cmd = new SQLiteCommand(_con);
                cmd.CommandText = "delete from class where cno = @cno";
                cmd.Prepare();
                cmd.Parameters.AddWithValue("@cno", id);
                count = cmd.ExecuteNonQuery();
            }
            catch (Exception) {
            }
            finally {
                _con.Close();
            }
            return count;
        }
        public static int DeleteSchoolByNo(string id){
            _con.Open();
            int count = 0;
            try {
                SQLiteCommand cmd = new SQLiteCommand(_con);
                cmd.CommandText = "delete from school where sno = @sno";
                cmd.Prepare();
                cmd.Parameters.AddWithValue("@sno", id);
                count = cmd.ExecuteNonQuery();
            }
            catch (Exception) {
            }
            finally {
                _con.Close();
            }
            return count;

        }
        public static int UpdateStudent(Student stu){
            int count = 0;
            _con.Open();
            try {
                SQLiteCommand cmd = new SQLiteCommand(_con);
                cmd.CommandText = "update  student set stu_name=@name,cno=@classNo,stu_sex=@sex where stu_no = @no";
                cmd.Prepare();
                cmd.Parameters.AddWithValue("@name", stu.Name);
                cmd.Parameters.AddWithValue("@classNo", stu.ClassNo);
                cmd.Parameters.AddWithValue("@sex", stu.Sex);
                cmd.Parameters.AddWithValue("@no", stu.No);
                count = cmd.ExecuteNonQuery();
            }
            catch (Exception) {

            }
            finally {
                _con.Close();
            }
            return count;
        }
        public static int UpdateClass(Class cla){
            int count = 0;
            _con.Open();
            try {
                SQLiteCommand cmd = new SQLiteCommand(_con);
                cmd.CommandText = "update class set cname=@name,sno=@sno where cno=@no";
                cmd.Prepare();
                cmd.Parameters.AddWithValue("@name", cla.Name);
                cmd.Parameters.AddWithValue("@sno", cla.SchNo);
                cmd.Parameters.AddWithValue("@no", cla.No);
                count = cmd.ExecuteNonQuery();
            }
            catch (Exception) {

            }
            finally {
                _con.Close();
            }

            return count;

        }

        public static int UpdateSchool(School sch){
            int count = 0;
            _con.Open();
            try {
                SQLiteCommand cmd = new SQLiteCommand(_con);
                cmd.CommandText = "update school set sname=@name,slocation=@location where sno=@no";
                cmd.Prepare();
                 cmd.Parameters.AddWithValue("@name", sch.Name);
                 cmd.Parameters.AddWithValue("@location", sch.Location);
                 cmd.Parameters.AddWithValue("@no", sch.No);
                count = cmd.ExecuteNonQuery();
            }
            catch (Exception) {

            }
            finally {
                _con.Close();
            }
            return count;
        }

        public static List<Student> SelectStudentList(){
            _con.Open();
            List<Student> stus = new List<Student>();
            try {
                string sql = "select * from student";
                SQLiteCommand cmd = new SQLiteCommand(sql, _con);
                SQLiteDataReader reader = cmd.ExecuteReader();

                while (reader.Read()) {
                    var no = reader["stu_no"].ToString();
                    var name = reader["stu_name"].ToString();
                    var cno = reader["cno"].ToString();
                    var sex = reader["stu_sex"].ToString();
                    stus.Add(new Student(no,name,cno,sex));
                }
            }
            catch (Exception) {
            }
            finally {
                _con.Close();
            }
            
            
            return stus;
        }
        public static List<Class> SelectClassList(){
            _con.Open();
            List<Class> classes = new List<Class>();
            try {
                string sql = "select * from class";
                SQLiteCommand cmd = new SQLiteCommand(sql, _con);
                SQLiteDataReader reader = cmd.ExecuteReader();
                while (reader.Read()) {
                    var no = reader["cno"].ToString();
                    var name = reader["cname"].ToString();
                    var sno = reader["sno"].ToString();
                    classes.Add(new Class(no, name, sno));
                }
            }
            catch (Exception) {
            }
            finally {
                _con.Close();
            }
            return classes;
        }

        public static List<School> SelectSchoolList(){
            _con.Open();
            List<School> schools = new List<School>();
            try {
                string sql = "select * from school";
                SQLiteCommand cmd = new SQLiteCommand(sql, _con);
                SQLiteDataReader reader = cmd.ExecuteReader();
                while (reader.Read()) {
                    var no = reader["sno"].ToString();
                    var name = reader["sname"].ToString();
                    var location = reader["slocation"].ToString();
                    schools.Add(new School(no, name, location));
                }
            }
            catch {
            }
            finally {
                _con.Close();
            }
            return schools;
        }

        public static int InsertLog(Log log){
            int count = 0;
            _con.Open();
            try {
                SQLiteCommand cmd = new SQLiteCommand(_con);
                cmd.CommandText = "insert into log(time,action) values(@time,@action)";
                cmd.Prepare();
                cmd.Parameters.AddWithValue("@time", log.Time);
                cmd.Parameters.AddWithValue("@action", log.Action);
                count = cmd.ExecuteNonQuery();
            }
            catch (Exception) {
            }
            finally {
                _con.Close();
            }
            return count;
        }

        public static List<Log> selectLogList(){
            _con.Open();
            List<Log> logs = new List<Log>();
            try {
                string sql = "select * from log";
                SQLiteCommand cmd = new SQLiteCommand(sql, _con);
                SQLiteDataReader reader = cmd.ExecuteReader();
                while (reader.Read()) {
                    var no = reader["lno"].ToString();
                    var time = reader["time"].ToString();
                    var action = reader["action"].ToString();
                    logs.Add(new Log(int.Parse(no),time, action));
                }
            }
            catch {
            }
            finally {
                _con.Close();
            }
            return logs;
        }


        public static SQLiteConnection GetConnection(){
            return _con;
        }

        public static void Close(){
            _con.Close();
        }

    }
}